package com.chengxiaoxiao.dao.impl;

import com.chengxiaoxiao.constant.DbConstants;
import com.chengxiaoxiao.dao.BookDao;
import com.chengxiaoxiao.dao.BorrowDao;
import com.chengxiaoxiao.dao.UsersDao;
import com.chengxiaoxiao.domain.*;
import com.chengxiaoxiao.domain.json.Author;
import com.chengxiaoxiao.domain.json.AuthorAndImage;
import com.chengxiaoxiao.utils.db.*;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

public class BorrowDaoImpl implements BorrowDao {

    private DBAssist da = new DBAssist(DBCPUtil.getDataSource());

    private UsersDao usersDao = new UsersDaoImpl();
    private BookDao bookDao = new BookDaoImpl();

    @Override
    public void add(Borrow borrow) {
        da.update("insert into borrow(subTime,userId,BookId,expireTime,isDeory,isReturn) values(?,?,?,?,?,?)",
                borrow.getSubTime(), borrow.getUserId(), borrow.getBookId(), plusDay(DbConstants.BORROW_DAYS_NUM, borrow.getSubTime()), DbConstants.BORRROW_ISDEORY_NODEORY, DbConstants.BORRROW_ISRETURN_FALSE);

    }

    /**
     * 指定日期增加天数
     *
     * @param num
     * @param newDate
     * @return
     * @throws ParseException
     */
    public static Date plusDay(int num, Date newDate) {
        Calendar ca = Calendar.getInstance();
        ca.add(Calendar.DATE, num);// num为增加的天数，可以改变的
        newDate = ca.getTime();
        return newDate;
    }

    @Override
    public void update(Borrow borrow) {
        da.update("update borrow set subTime=?,userId=?,BookId=?,expireTime=?,isDeory=?,isReturn=? where id=?",
                borrow.getSubTime(), borrow.getUserId(), borrow.getBookId(), plusDay(60, borrow.getSubTime()), borrow.getIsDeory(), borrow.getIsReturn(), borrow.getId());
    }

    @Override
    public void delete(long id) {
        da.update("delete from borrow where id=?", id);
    }

    @Override
    public List get() {

        List<Borrow> list = (List<Borrow>) da.query("select * from borrow", new BeanListHandler(Borrow.class));

        List<Borrow> model = new ArrayList<>();

        for (int i = 0; i < list.size(); i++) {

            Borrow b = list.get(i);
            Users user = usersDao.getbyId(b.getUserId());
            b.setUser(user);
            Books book = bookDao.getbyId(b.getBookId());
            b.setBook(book);
            model.add(b);

        }

        return model;
    }

    @Override
    public Borrow getbyId(long id) {
        Borrow b = (Borrow) da.query("select * from borrow where id=?", new BeanHandler(Borrow.class),id);

        Users user = usersDao.getbyId(b.getUserId());
        b.setUser(user);
        Books book = bookDao.getbyId(b.getBookId());
        b.setBook(book);

        return b;
    }

    @Override
    public List getListPage(int startIndex, int pageSize) {

        List<Borrow> list = (List<Borrow>) da.query("select * from borrow limit ?,?", new BeanListHandler(Borrow.class), startIndex, pageSize);
        List<Borrow> model = new ArrayList<>();

        for (int i = 0; i < list.size(); i++) {

            Borrow b = list.get(i);
            Users user = usersDao.getbyId(b.getUserId());
            b.setUser(user);
            Books book = bookDao.getbyId(b.getBookId());
            b.setBook(book);
            model.add(b);

        }

        return model;
    }

    @Override
    public int getTotalRecordsNum() {

        return (int) da.query("select Count(*) from borrow", new ScalarHandler());
    }

    @Override
    public int getTotalRecordsNumBorrow() {
        return (int) da.query("select count(*) from borrow where isReturn=0", new ScalarHandler());
    }

    @Override
    public List<Borrow> getListPageBorrow(int startIndex, int pageSize) {
        List<Borrow> list = (List<Borrow>) da.query("select * from borrow where isReturn=0 limit ?,?", new BeanListHandler(Borrow.class), startIndex, pageSize);
        List<Borrow> model = new ArrayList<>();

        for (int i = 0; i < list.size(); i++) {

            Borrow b = list.get(i);
            Users user = usersDao.getbyId(b.getUserId());
            b.setUser(user);
            Books book = bookDao.getbyId(b.getBookId());
            b.setBook(book);

            model.add(b);

        }

        return model;
    }

    /**
     * 未归还书籍
     *
     * @return
     */
    @Override
    public int getTotalRecordsNumNoReturn() {
        return (int) da.query("select count(*) from borrow where isReturn=0 and expireTime<?", new ScalarHandler(), new Date());
    }

    @Override
    public List<Borrow> getListPageBorrowNoReturn(int startIndex, int pageSize) {
        List<Borrow> list = (List<Borrow>) da.query("select * from borrow where isReturn=0 and expireTime<? limit ?,?", new BeanListHandler(Borrow.class), new Date(), startIndex, pageSize);
        List<Borrow> model = new ArrayList<>();

        for (int i = 0; i < list.size(); i++) {

            Borrow b = list.get(i);
            Users user = usersDao.getbyId(b.getUserId());
            b.setUser(user);
            Books book = bookDao.getbyId(b.getBookId());
            b.setBook(book);

            model.add(b);

        }

        return model;
    }

    @Override
    public int getTotalRecordsNumRank() {
        return (int) da.query("select count(*) from books INNER JOIN (select count(*) c,bookid s from borrow group by bookId order by c desc) t on t.s=books.id", new ScalarHandler());

    }

    @Override
    public List<Books> getListPageBorrowRank(int startIndex, int pageSize) {
        String sql = "select books.* from books INNER JOIN (select count(*) c,bookid s from borrow group by bookId order by c desc) t on t.s=books.id limit ?,?";
        List<Books> list = (List<Books>) da.query(sql, new BeanListHandler(Books.class), startIndex, pageSize);
        List<Books> model = new ArrayList<>();
        for (int i = 0; i < list.size(); i++) {
            Books book = list.get(i);
            if (book != null) {
                Category category = (Category) da.query("select * from category where id=?", new BeanHandler(Category.class), book.getCategory_Id());
                book.setCategory(category);

                Publisher publisher = (Publisher) da.query("select * from publisher where id=?", new BeanHandler(Publisher.class), book.getPublisherId());
                book.setPublisher(publisher);
            }
            model.add(book);
        }

        return model;
    }

    @Override
    public int getTotalRecordsNumDestory() {
        return (int) da.query("select count(*) from borrow where isDeory=1", new ScalarHandler());

    }

    /**
     * 损坏书籍
     * @param startIndex
     * @param pageSize
     * @return
     */
    @Override
    public List<Borrow> getListPageBorrowDestory(int startIndex, int pageSize) {
        List<Borrow> list = (List<Borrow>) da.query("select * from borrow where isDeory=1 limit ?,?", new BeanListHandler(Borrow.class), startIndex, pageSize);
        List<Borrow> model = new ArrayList<>();

        for (int i = 0; i < list.size(); i++) {

            Borrow b = list.get(i);
            Users user = usersDao.getbyId(b.getUserId());
            b.setUser(user);
            Books book = bookDao.getbyId(b.getBookId());
            b.setBook(book);

            model.add(b);
        }

        return model;
    }

    @Override
    public List<Books> getListPageNewBook(int startIndex, int pageSize) {
        String sql = "select * from books order by subTime limit ?,?";
        List<Books> list = (List<Books>) da.query(sql, new BeanListHandler(Books.class), startIndex, pageSize);
        List<Books> model = new ArrayList<>();
        for (int i = 0; i < list.size(); i++) {
            Books book = list.get(i);
            if (book != null) {
                Category category = (Category) da.query("select * from category where id=?", new BeanHandler(Category.class), book.getCategory_Id());
                book.setCategory(category);

                Publisher publisher = (Publisher) da.query("select * from publisher where id=?", new BeanHandler(Publisher.class), book.getPublisherId());
                book.setPublisher(publisher);
            }
            model.add(book);
        }

        return model;

    }

    @Override
    public int getTotalHotAuthor() {
        return (int) da.query("select count(*) from (\n" +
                " select DISTINCT books.author from books INNER JOIN (select count(*) c,bookid s from borrow group by bookId order by c desc) t on t.s=books.id \n" +
                ") z", new ScalarHandler());

    }

    @Override
    public List getListPageHotAuthor(int startIndex, int pageSize) {
        String sql ="select DISTINCT books.author from books INNER JOIN (select count(*) c,bookid s from borrow group by bookId order by c desc) t on t.s=books.id limit ?,?";

        List list = (List) da.query(sql,new BeanListHandler(Author.class),startIndex,pageSize);
        return list;
    }

    @Override
    public AuthorAndImage getAuthorAndImage(String author) {
        String sql ="select author name,imagesSrc src from books where author=? limit 0,1";

         return (AuthorAndImage) da.query(sql,new BeanHandler(AuthorAndImage.class),author);
    }

    @Override
    public int getBorrowCountByUser(int uid) {
        return (int) da.query("select Count(*) from borrow where userId=? and isReturn=0", new ScalarHandler(),uid);
    }

    @Override
    public Borrow getBorrow(String userid, String bookId) {
        Borrow b = (Borrow) da.query("select * from borrow where userId=? and bookId=?", new BeanHandler(Borrow.class),userid,bookId);

        Users user = usersDao.getbyId(b.getUserId());
        b.setUser(user);
        Books book = bookDao.getbyId(b.getBookId());
        b.setBook(book);

        return b;
    }

    @Override
    public List<Borrow> getBorrow(String userId) {
        List<Borrow> list  = (List<Borrow>) da.query("select * from borrow where userId=?", new BeanListHandler(Borrow.class),userId);
        List<Borrow> model = new ArrayList<>();

        for (int i = 0; i < list.size(); i++) {

            Borrow b = list.get(i);
            Users user = usersDao.getbyId(b.getUserId());
            b.setUser(user);
            Books book = bookDao.getbyId(b.getBookId());
            b.setBook(book);

            model.add(b);
        }

        return model;
    }

    @Override
    public Borrow getBorrowNoReturn(String s, String bookId) {
        Borrow b = (Borrow) da.query("select * from borrow where userId=? and bookId=? and isReturn=0", new BeanHandler(Borrow.class),s,bookId);
        if(null!=b)
        {
            Users user = usersDao.getbyId(b.getUserId());
            b.setUser(user);
            Books book = bookDao.getbyId(b.getBookId());
            b.setBook(book);
        }
        return b;
    }

    @Override
    public List<Borrow> getExpire() {

        Date date = new Date();
        SimpleDateFormat f = new SimpleDateFormat("yyyy-MM-dd");

        Calendar c =Calendar.getInstance();
        c.add(Calendar.DATE,-10);
        date = c.getTime();
        String now =f.format(date);


        List<Borrow> list  = (List<Borrow>) da.query("select * from borrow where expireTime<? and isReturn=0", new BeanListHandler(Borrow.class),now);
        List<Borrow> model = new ArrayList<>();

        for (int i = 0; i < list.size(); i++) {

            Borrow b = list.get(i);
            Users user = usersDao.getbyId(b.getUserId());
            b.setUser(user);
            Books book = bookDao.getbyId(b.getBookId());
            b.setBook(book);

            model.add(b);
        }

        return model;
    }


}
